/**
 * A demo program to show how jdbc works with postgresql
 * Nick Fankhauser 10/25/01
 * nickf@ontko.com or nick@fankhausers.com
 * This program may be freely copied and modified
 * Please keep this header intact on unmodified versions
 * The rest of the documentation that came with this demo program
 * may be found at http://www.fankhausers.com/postgresql/jdbc
 */



import java.sql.*;   // All we need for JDBC
import java.text.*;
import java.util.Calendar;
import java.io.*;


import org.apache.lucene.analysis.standard.StandardAnalyzer;
import org.apache.lucene.index.CorruptIndexException;
import org.apache.lucene.index.IndexWriter;



public class HelloPostgre
{
  Connection       db;        // A connection to the database
  Statement        sql;       // Our statement to run queries with
  DatabaseMetaData dbmd;      // This is basically info the driver delivers
                              // about the DB it just connected to. I use
                              // it to get the DB version to confirm the
                              // connection in this example.
  static final File INDEX_DIR = new File("track_full.index");
  static final int  WINDOW_SIZE = 10000;
  
  public HelloPostgre(String argv[])
    throws ClassNotFoundException, SQLException, CorruptIndexException, IOException
  {
    String database = argv[0];
    String username = argv[1];
    String password = argv[2];
    Class.forName("org.postgresql.Driver"); //load the driver
    db = DriverManager.getConnection("jdbc:postgresql:"+database,
                                     username,
                                     password); //connect to the db
    dbmd = db.getMetaData(); //get MetaData to confirm connection
    System.out.println("Connection to "+dbmd.getDatabaseProductName()+" "+
                       dbmd.getDatabaseProductVersion()+" successful.\n");
    sql = db.createStatement(); //create a statement that we can use later

    IndexWriter writer = null;
   
   	writer = new IndexWriter(INDEX_DIR, new StandardAnalyzer(), true);
    
    
    
/*
    String sqlText = "create table jdbc_demo (code int, text varchar(20))";
*/
   	
   	int maxa = WINDOW_SIZE;
   	int mina = 0;
   	int count = 0;
   	System.out.println(INDEX_DIR.getCanonicalPath());
   	boolean flag = true;
   	
   	while (flag) {
  // 	ResultSet results = sql.executeQuery("select id, name, gid from artist where id < " + maxa + " and id > " + mina );
//   		ResultSet results = sql.executeQuery("select track.name as trackname, artist.name as artistname from track, artist where track.artist=artist.id and track.id < " + maxa + " and track.id > " + mina );

/*  
   		ResultSet results = sql.executeQuery("SELECT track.name as trackname, artist.name as artistname, album.name as albumname " + 
   											 "FROM track, artist, albumjoin, album " +
   											 "WHERE track.artist=artist.id and track.id=albumjoin.track and albumjoin.album=album.id " +
   											 "and track.id < " + maxa + " and track.id > " + mina );
*/
   		
	ResultSet results = sql.executeQuery("SELECT track.name as trackname, artist.name as artistname, album.name as albumname, album_amazon_asin.asin as albumasin "+
										 "from track, artist, album,  albumjoin " +
										 "LEFT OUTER JOIN album_amazon_asin " +
										 "ON albumjoin.album=album_amazon_asin.album "+ 	
										 "WHERE track.artist=artist.id and track.id=albumjoin.track and albumjoin.album=album.id and track.id <" + maxa + " and track.id > " + mina );

   		
   		
    if (results != null  )
    {
    	//System.out.println(results.getInt("rsize"));
      count = 0;
      while (results.next())
      {    	   	    
    	     		 
        //System.out.println(results.getString("name") + "\n");
        writer.addDocument(TrackDocument.TrackDocument(results.getString("trackname"), 
        											   results.getString("albumname"), 
        											   results.getString("artistname"),
        											   results.getString("albumasin")));        
        
        count ++;                
      }
      // if the recordset returns no rows then i quit!
      if (count==0) flag = false; 
    }
   	
    results.close();
    
    mina = maxa + 1;
    maxa = maxa + WINDOW_SIZE;
    System.out.println(HelloPostgre.now() + ":  range: " + mina + " - " + maxa );
   	
   	}


    System.out.println("bye!");
    
    writer.optimize();
    writer.close();
    
    
/*
    sqlText = "drop table jdbc_demo";
    System.out.println("Executing this command: "+sqlText+"\n");
    sql.executeUpdate(sqlText);
*/

    db.close();
  }

  public static void correctUsage()
  {
    System.out.println("\nIncorrect number of arguments.\nUsage:\n "+
                       "java   \n");
    System.exit(1);
  }
  
  public static final String DATE_FORMAT_NOW = "yyyy-MM-dd HH:mm:ss";
  public static String now() {
	    Calendar cal = Calendar.getInstance();
	    SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT_NOW);
	    return sdf.format(cal.getTime());

	  }
  
  public static void main (String args[])
  {
	 String argss[] = { "mbrainz" , "root", "root" };
	 
    try
    {
      HelloPostgre demo = new HelloPostgre(argss);
    }
    catch (Exception ex)
    {
      System.out.println("***Exception:\n"+ex);
      ex.printStackTrace();
    }
  }
}